package ncs.common.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import ncs.common.model.User;
import ncs.common.util.ConnectDB;

/**
 * @author Jian
 * +-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| db_id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| userID    | varchar(20) | YES  |     | NULL    |                |
| password  | varchar(20) | YES  |     | NULL    |                |
| authority | int(11)     | NO   |     | NULL    |                |
| fails     | int(3)      | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
 *
 */
public class UserDAO implements IUserDAO{
	private int fails=-1;
	@Override
	public User login(String id, String pwd) throws Exception {
		Connection conn=ConnectDB.getConnection();
		String sql="select * from tb_user where userID=? AND password=?";
		PreparedStatement ps=conn.prepareStatement(sql);
		ps.setString(1, id);
		ps.setString(2, pwd);
		ResultSet rs=ps.executeQuery();
		User user=null;
		if(rs.next())
		{
			user=new User();
			user.setDbID(rs.getInt(1));
			user.setUserID(rs.getString(2));
			user.setPassword("");		//密码没必要获取
			user.setAuthority(rs.getInt(4));
		}
		ps.close();
		if(user==null)
		{
			//验证错误，检测该帐号是否已经错误多次，如果是则记录
			//如果将错误次数记录在Session里，帐号还是有存在被暴力破解的危险
			sql="select db_id,fails from tb_user where userID=?";
			ps=conn.prepareStatement(sql);
			ps.setString(1, id);
			rs=ps.executeQuery();
			ps.close();
			if(rs.next())
			{
				int db_id=rs.getInt(1);
				fails=rs.getInt(2);
				++fails;
				//存在该用户则要在数据库做记录
				sql="update tb_user set fails=tb_user.fails+1 where db_id=?";
				ps=conn.prepareStatement(sql);
				ps.setInt(1, db_id);
				ps.execute();
				ps.close();
			}
		}
		else
		{
			//登录成功，则要重置fails字段
			sql="update tb_user set fails=0 where db_id=?";
			ps=conn.prepareStatement(sql);
			ps.setInt(1, user.getDbID());
			ps.execute();
		}
		conn.close();
		return user;
	}
	/**
	 * @return 返回用户登录错误的次数，如果Login返回null则调用该函数可查看用户错误次数
	 */
	public int getFails()
	{
		return this.fails;
	}
	
	/**
	 * 向数据库中添加一个用户，包括管理员，老师和学生
	 * @param user
	 */
	public static int addUser(User user,Connection conn)throws Exception
	{
		//Connection conn=ConnectDB.getConnection();
		boolean bconn=(conn==null?false:true);
		if(conn==null)
			conn=ConnectDB.getConnection();
		String sql="insert tb_user values(?,?,?,?,?)";
		PreparedStatement ps=conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
		ps.setInt(1, 0);
		ps.setString(2, user.getUserID());
		ps.setString(3, user.getPassword());
		ps.setInt(4, user.getAuthority());
		ps.setInt(5, 0);
		ps.execute();
		ResultSet rs=ps.getGeneratedKeys();
		rs.next();
		int key=rs.getInt(1);
		ps.close();
		if(false==bconn)
			conn.close();
		return key;
	}
	
	/**
	 * 验证指定用户是否存在
	 * @param id	用户号码
	 * @return
	 * @throws Exception
	 */
	public static boolean isUserExist(String id) throws Exception
	{
		Connection conn=ConnectDB.getConnection();
		String sql="select count(*) from tb_user where userID='"+id+"'";
		Statement st=conn.createStatement();
		ResultSet rs=st.executeQuery(sql);
		rs.next();
		boolean ret=rs.getInt(1)==1;
		rs.close();
		st.close();
		conn.close();
		return ret;
	}
	public static boolean isUserExist(int id) throws Exception
	{
		Connection conn=ConnectDB.getConnection();
		String sql="select count(*) from tb_user where db_id="+id;
		Statement st=conn.createStatement();
		ResultSet rs=st.executeQuery(sql);
		rs.next();
		boolean ret=rs.getInt(1)==1;
		rs.close();
		st.close();
		conn.close();
		return ret;
	}
	public static int getUserDbId(String userid) throws Exception
	{
		Connection conn=ConnectDB.getConnection();
		String sql="select db_id from tb_user where userID="+userid;
		Statement st=conn.createStatement();
		ResultSet rs=st.executeQuery(sql);
		rs.next();
		int ret=rs.getInt(1);
		rs.close();
		st.close();
		conn.close();
		return ret;
	}
	@Override
	public void updateField(int id,String field, Object value,Connection conn) throws Exception {
		boolean bconn=(conn==null?false:true);
		String sql=String.format("update tb_user set %s=? where db_id=?", field);
		if(conn==null)
			conn=ConnectDB.getConnection();
		PreparedStatement ps=conn.prepareStatement(sql);
		if(field.equals("password")||field.equals("userID"))
		{
			ps.setString(1, (String)value);
		}
		else
		{
			ps.setInt(1, (Integer)value);
		}
		ps.setInt(2, id);
		try
		{
			ps.execute();
		}catch(Exception e)
		{
			ps.close();
			if(false==bconn)conn.close();
			throw e;
		}
		ps.close();
		if(false==bconn)conn.close();
	}
	@Override
	public void deleteUser(int[] id,Connection conn) throws Exception {
		boolean bconn=(conn==null?false:true);
		String sql="delete from tb_user where db_id=?";
		if(conn==null)
			conn=ConnectDB.getConnection();
		PreparedStatement ps=conn.prepareStatement(sql);
		try {
			for (int i : id) {
				ps.setInt(1, i);
				ps.execute();
			}
		} catch (Exception e) {
			ps.close();
			if(false==bconn)conn.close();
			throw e;
		}
		ps.close();
		if(false==bconn)conn.close();
	}
	@Override
	public void deleteUser(int id,Connection conn) throws Exception {
		int[]idex=new int[1];
		idex[0]=id;
		this.deleteUser(idex, conn);
	}
}